package elasticsearch;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.*;
import com.alibaba.druid.sql.ast.expr.*;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
import com.alibaba.druid.sql.parser.Token;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author dushougudu
 */
public class SqlBuilder {
    MySqlSelectQueryBlock queryBlock;

    private SqlBuilder() {
        queryBlock = new MySqlSelectQueryBlock();
    }

    private SqlBuilder(SQLSelectQuery query) {
        this.queryBlock = (MySqlSelectQueryBlock) query;
    }


    public static SqlBuilder create() {
        return new SqlBuilder();
    }

    public static SqlBuilder create(String sql) {
        SQLSelectStatement sqlStatement = (SQLSelectStatement) SQLUtils.parseSingleMysqlStatement(sql);
        return new SqlBuilder(sqlStatement.getSelect().getQuery());
    }

    public SqlBuilder select(String... fields) {
        return select(Arrays.asList(fields));

    }

    public SqlBuilder select(Collection<String> fields) {
        if (CollectionUtils.isEmpty(fields)) {
            queryBlock.addSelectItem(new SQLIdentifierExpr(Token.STAR.name));
        } else {
            fields.forEach(f -> queryBlock.addSelectItem(new SQLIdentifierExpr(f)));
        }
        return this;
    }

    public SqlBuilder from(String... tables) {
        return from(Arrays.asList(tables));
    }

    public SqlBuilder from(Collection<String> tables) {
        String tableName;
        if (CollectionUtils.isEmpty(tables)) {
            tableName = Token.STAR.name;
        } else {
            tableName = String.join(Token.COMMA.name, tables);
        }
        SQLExprTableSource table = new SQLExprTableSource();
        table.setExpr(tableName);
        queryBlock.setFrom(table);
        return this;
    }

    public SqlBuilder fromFunction(String functionName, Collection<String> tables, String... parameters) {
        String tableName;
        if (CollectionUtils.isEmpty(tables)) {
            tableName = Token.STAR.name;
        } else {
            tableName = String.join(Token.COMMA.name, tables);
        }
        String[] params = new String[parameters.length + 1];
        params[0] = tableName;
        System.arraycopy(parameters, 0, params, 1, parameters.length);
        SQLMethodInvokeExpr expr = this.getWhere().createFunction(functionName, params);
        SQLExprTableSource table = new SQLExprTableSource();
        table.setExpr(expr);
        queryBlock.setFrom(table);
        return this;
    }

    public List<String> getFromTables() {
        List<String> tables = new ArrayList<>();
        SQLTableSource tableSource = queryBlock.getFrom();
        getFromTables(tableSource, tables);
        return tables;
    }

    public void getFromTables(SQLTableSource tableSource, List<String> tables) {
        if (tableSource instanceof SQLJoinTableSource) {
            SQLJoinTableSource source = (SQLJoinTableSource) tableSource;
            getFromTables(source.getLeft(), tables);
            getFromTables(source.getRight(), tables);
        } else if (tableSource instanceof SQLExprTableSource) {
            SQLExprTableSource source = (SQLExprTableSource) tableSource;
            SQLExpr tableName = source.getExpr();
            if (tableName instanceof SQLName) {
                tables.add(((SQLName) tableName).toString());
            }
        }
    }


    public SqlBuilder orderBy(String field, String order) {
        SQLOrderBy orderBy = this.queryBlock.getOrderBy();
        if (orderBy == null) {
            orderBy = new SQLOrderBy();
            this.queryBlock.setOrderBy(orderBy);
        }
        SQLSelectOrderByItem item = new SQLSelectOrderByItem();
        item.setExpr(new SQLIdentifierExpr(field));
        if (StringUtils.isNoneBlank(order)) {
            item.setType(SQLOrderingSpecification.valueOf(order.toUpperCase()));
        }
        orderBy.addItem(item);
        return this;
    }

    public SqlBuilder groupBy(List<String> groups) {
        if (CollectionUtils.isEmpty(groups)) {
            return this;
        }
        SQLSelectGroupByClause group = this.queryBlock.getGroupBy();
        if (ObjectUtils.isEmpty(group)) {
            group = new SQLSelectGroupByClause();
            this.queryBlock.setGroupBy(group);
        }
        SQLSelectGroupByClause finalGroup = group;
        groups.stream().forEach(field -> finalGroup.addItem(new SQLIdentifierExpr(field)));
        return this;
    }

    public SqlBuilder groupBy(String... fields) {
        if (ObjectUtils.isEmpty(fields)) {
            return this;
        }
        SQLSelectGroupByClause group = this.queryBlock.getGroupBy();
        if (ObjectUtils.isEmpty(group)) {
            group = new SQLSelectGroupByClause();
            this.queryBlock.setGroupBy(group);
        }
        SQLSelectGroupByClause finalGroup = group;
        Arrays.stream(fields).forEach(field -> finalGroup.addItem(new SQLIdentifierExpr(field)));

        return this;
    }

    public SqlBuilder groupFunction(String functionName, String... parameters) {
        if (StringUtils.isEmpty(functionName)) {
            return this;
        }
        SQLSelectGroupByClause group = this.queryBlock.getGroupBy();
        if (ObjectUtils.isEmpty(group)) {
            group = new SQLSelectGroupByClause();
            this.queryBlock.setGroupBy(group);
        }
        SQLMethodInvokeExpr expr = this.getWhere().createFunction(functionName, parameters);
        group.addItem(expr);
        return this;
    }

    public SqlBuilder limit(int pageNo, int pageSize) {
        SQLLimit limit = new SQLLimit();
        limit.setOffset(pageSize * (pageNo - 1));
        limit.setRowCount(pageSize);
        this.queryBlock.setLimit(limit);
        return this;
    }

    public String build() {
        return SQLUtils.toSQLString(queryBlock);
    }


    public SqlBuilder where(WhereBuilder whereBuilder) {
        this.queryBlock.setWhere(whereBuilder.build());
        return this;
    }

    public SqlBuilder where(String where) {
        this.queryBlock.setWhere(WhereBuilder.create(where).build());
        return this;
    }

    public WhereBuilder getWhere() {
        return WhereBuilder.create(this.queryBlock.getWhere());
    }

    public boolean containsWhere(String name) {
        return traverse(name, this.queryBlock.getWhere());
    }

    public boolean notContainsWhere(String name) {
        return !traverse(name, this.queryBlock.getWhere());
    }

    private boolean traverse(String name, SQLExpr sqlExpr) {
        if (sqlExpr instanceof SQLBinaryOpExpr) {
            SQLBinaryOpExpr binaryOpExpr = (SQLBinaryOpExpr) sqlExpr;
            return traverse(name, binaryOpExpr.getLeft()) || traverse(name, binaryOpExpr.getRight());
        } else if (sqlExpr instanceof SQLIdentifierExpr) {
            SQLIdentifierExpr sqlIdentifierExpr = (SQLIdentifierExpr) sqlExpr;
            return sqlIdentifierExpr.getName().equals(name);
        }
        return false;
    }


    public static class WhereBuilder {
        private SQLExpr where;
        private List<String> workItemTypeKeys;

        private WhereBuilder() {
        }

        private WhereBuilder(String sqlExpr) {
            where = SQLUtils.toMySqlExpr(sqlExpr);
        }

        private WhereBuilder(SQLExpr sqlExpr) {
            where = sqlExpr;
        }

        public static WhereBuilder create() {
            return new WhereBuilder();
        }

        public static WhereBuilder create(String sqlExpr) {
            return new WhereBuilder(sqlExpr);
        }

        public static WhereBuilder create(SQLExpr sqlExpr) {
            return new WhereBuilder(sqlExpr);
        }

        public void setWorkItemTypeKeys(List<String> workItemTypeKeys) {
            this.workItemTypeKeys = workItemTypeKeys;
        }

        public WhereBuilder and(FilterItem filterItem) {
            if (where != null) {
                return and(where);
            } else {
                return and(filterItem.getFilterId(), filterItem.getOperator(), filterItem.getFilterValue());
            }
        }

        public WhereBuilder and(SQLExpr sqlExpr) {
            append(sqlExpr, SQLBinaryOperator.BooleanAnd);
            return this;
        }

        private WhereBuilder and(Object object) {
            if (object instanceof FilterItem) {
                and((FilterItem) object);
            } else if (object instanceof SQLExpr) {
                and((SQLExpr) object);
            } else {
                and(object.toString());
            }
            return this;
        }

        public WhereBuilder and(String sqlExpr) {
            and(SQLUtils.toMySqlExpr(sqlExpr));
            return this;
        }

        public WhereBuilder and(Collection<?> sqlExpressions) {
            if (CollectionUtils.isNotEmpty(sqlExpressions)) {
                sqlExpressions.forEach(this::and);
            }
            return this;
        }

        public WhereBuilder and(WhereBuilder builder) {
            append(builder.build(), SQLBinaryOperator.BooleanAnd);
            return this;
        }

        public WhereBuilder andFunction(String functionName, String... parameters) {
            and(createFunction(functionName, parameters));
            return this;
        }

        private SQLMethodInvokeExpr createFunction(String functionName, String... parameters) {
            SQLMethodInvokeExpr func = new SQLMethodInvokeExpr(functionName);
            for (String parameter : parameters) {
                func.addArgument(new SQLIdentifierExpr(StringUtils.wrap(parameter, "'")));
            }
            return func;
        }

        public WhereBuilder andFunction(String functionName, WhereBuilder where, String... parameters) {
            and(createFunction(functionName, where, parameters));
            return this;
        }

        private SQLMethodInvokeExpr createFunction(String functionName, WhereBuilder where, String... parameters) {
            SQLMethodInvokeExpr func = new SQLMethodInvokeExpr(functionName);
            func.addArgument(where.build());
            for (String parameter : parameters) {
                func.addArgument(new SQLIdentifierExpr(StringUtils.wrap(parameter, "'")));
            }
            return func;
        }

        public WhereBuilder andFunction(String functionName, WhereBuilder where) {
            and(createFunction(functionName, where));
            return this;
        }

        private SQLMethodInvokeExpr createFunction(String functionName, WhereBuilder where) {
            SQLMethodInvokeExpr func = new SQLMethodInvokeExpr(functionName);
            func.addArgument(where.build());
            return func;
        }


        public WhereBuilder andWithBetween(String var, Object begin, Object end) {
            append(between(var, begin, end), SQLBinaryOperator.BooleanAnd);
            return this;
        }

        public WhereBuilder andWithIn(String var, Collection<?> values) {
            if (CollectionUtils.isEmpty(values)) {
                return this;
            }
            append(in(var, values), SQLBinaryOperator.BooleanAnd);
            return this;
        }

        public WhereBuilder andWithColection(String var, Collection<?> values) {
            if (CollectionUtils.isEmpty(values)) {
                return this;
            }
            for (Object value : values
                    ) {
                append(condition(var, SQLBinaryOperator.Equality, value), SQLBinaryOperator.BooleanAnd);
            }
            return this;
        }

        public WhereBuilder and(String key, String operator, Object value) {
            if (value == null) {
                return this;
            }
            if (value instanceof String) {
                String str = (String) value;
                if (StringUtils.isBlank(str)) {
                    return this;
                }
            }
            String operatorUpper = operator.toUpperCase();
            switch (operatorUpper) {
                case "LIKE":
                    String val = value.toString().replaceAll("'", "").toLowerCase();
                    return and(condition(key, SQLBinaryOperator.Like, StringUtils.wrap(value.toString(), "%")));
                case "IN":
                    if (value instanceof List<?>) {
                        return and(in(key, (List<?>) value));
                    }
                    return and(in(key, Arrays.asList(value.toString().split(Token.COMMA.name))));
                case "NOT IN":
                    if (value instanceof List<?>) {
                        return and(notIn(key, (List<?>) value));
                    }
                    return and(notIn(key, Arrays.asList(value.toString().split(Token.COMMA.name))));
                case "NOT CONTAINS":
                    return and(notCondition(key, getBinaryOperator(operatorUpper), value));
                case "BETWEEN":
                    String begin = StringUtils.substringBefore(value.toString(), Token.COMMA.name);
                    String end = StringUtils.substringAfter(value.toString(), Token.COMMA.name);
                    if (StringUtils.isNoneEmpty(begin)) {
                        and(condition(key, SQLBinaryOperator.GreaterThanOrEqual, begin));
                    }
                    if (StringUtils.isNoneEmpty(end)) {
                        and(condition(key, SQLBinaryOperator.LessThanOrEqual, end));
                    }
                    return this;
                case "AND":
                    return andWithColection(key, Arrays.asList(value.toString().split(Token.COMMA.name)));
                case "OR":
                    return andWithIn(key, Arrays.asList(value.toString().split(Token.COMMA.name)));
                default:
                    return and(condition(key, getBinaryOperator(operatorUpper), value));
            }
        }

        private SQLBinaryOperator getBinaryOperator(String operator) {
            switch (operator) {
                case "IS": {
                    return SQLBinaryOperator.Is;
                }
                case "IS NOT": {
                    return SQLBinaryOperator.IsNot;
                }
                case "=":
                case "CONTAINS":
                case "NOT CONTAINS": {
                    return SQLBinaryOperator.Equality;
                }
                case "!=": {
                    return SQLBinaryOperator.NotEqual;
                }
                case ">": {
                    return SQLBinaryOperator.GreaterThan;
                }
                case ">=": {
                    return SQLBinaryOperator.GreaterThanOrEqual;
                }
                case "<": {
                    return SQLBinaryOperator.LessThan;
                }
                case "<=": {
                    return SQLBinaryOperator.LessThanOrEqual;
                }
                default: {
                    throw new IllegalArgumentException("wrong operator" + operator);
                }
            }
        }

        public WhereBuilder or(WhereBuilder builder) {
            append(builder.build(), SQLBinaryOperator.BooleanOr);
            return this;
        }

        public WhereBuilder or(SQLExpr sqlExpr) {
            append(sqlExpr, SQLBinaryOperator.BooleanOr);
            return this;
        }

        public WhereBuilder or(String sqlExpr) {
            or(SQLUtils.toMySqlExpr(sqlExpr));
            return this;
        }

        public WhereBuilder or(FilterItem filterItem) {
            if (where != null) {
                return or(where);
            } else {
                return or(filterItem.getFilterId(), filterItem.getOperator(), filterItem.getFilterValue());
            }
        }

        public WhereBuilder or(Collection<?> sqlExpressions) {
            if (CollectionUtils.isNotEmpty(sqlExpressions)) {
                sqlExpressions.forEach(this::or);
            }
            return this;
        }

        private WhereBuilder or(Object object) {
            if (object instanceof FilterItem) {
                or((FilterItem) object);
            } else if (object instanceof SQLExpr) {
                or((SQLExpr) object);
            } else {
                or(object.toString());
            }
            return this;
        }

        private WhereBuilder or(String key, String operator, Object value) {
            if (value == null) {
                return this;
            }
            if (value instanceof String) {
                String str = (String) value;
                if (StringUtils.isBlank(str)) {
                    return this;
                }
            }
            String operatorUpper = operator.toUpperCase();
            switch (operatorUpper) {
                case "LIKE":
                    return or(condition(key, SQLBinaryOperator.Like, StringUtils.wrap(value.toString(), "%")));
                case "IN":
                    if (value instanceof List<?>) {
                        return orWithIn(key, (List<?>) value);
                    }
                    return orWithIn(key, Arrays.asList(value.toString().split(Token.COMMA.name)));
                case "NOT IN":
                    if (value instanceof List<?>) {
                        return orWithNotIn(key, (List<?>) value);
                    }
                    return orWithNotIn(key, Arrays.asList(value.toString().split(Token.COMMA.name)));
                case "NOT CONTAINS":
                    return or(notCondition(key, getBinaryOperator(operatorUpper), value));
                default:
                    return or(condition(key, getBinaryOperator(operatorUpper), value));
            }
        }

        public WhereBuilder orWithBetween(String var, Object begin, Object end) {
            append(between(var, begin, end), SQLBinaryOperator.BooleanOr);
            return this;
        }

        public WhereBuilder orWithIn(String var, List<?> values) {
            if (CollectionUtils.isEmpty(values)) {
                return this;
            }
            append(in(var, values), SQLBinaryOperator.BooleanOr);
            return this;
        }

        public WhereBuilder orWithNotIn(String var, List<?> values) {
            if (CollectionUtils.isEmpty(values)) {
                return this;
            }
            append(notIn(var, values), SQLBinaryOperator.BooleanOr);
            return this;
        }

        public SQLExpr build() {
            return where;
        }

        private SQLBinaryOpExpr condition(String var, SQLBinaryOperator operator, Object val) {
            SQLBinaryOpExpr condition = new SQLBinaryOpExpr();
            condition.setLeft(new SQLIdentifierExpr(var));
            condition.setOperator(operator);
            condition.setRight(convert(val));
            return condition;
        }

        private SQLNotExpr notCondition(String var, SQLBinaryOperator operator, Object val) {
            SQLBinaryOpExpr condition = new SQLBinaryOpExpr();
            condition.setLeft(new SQLIdentifierExpr(var));
            condition.setOperator(operator);
            condition.setRight(convert(val));
            return new SQLNotExpr(condition);
        }

        private SQLBetweenExpr between(String var, Object begin, Object end) {
            SQLBetweenExpr sqlBetweenExpr = new SQLBetweenExpr();
            sqlBetweenExpr.setTestExpr(new SQLIdentifierExpr(var));
            sqlBetweenExpr.setBeginExpr(convert(begin));
            sqlBetweenExpr.setEndExpr(convert(end));
            return sqlBetweenExpr;
        }

        private SQLInListExpr in(String var, Collection<?> values) {
            SQLInListExpr sqlInListExpr = new SQLInListExpr();
            sqlInListExpr.setExpr(new SQLIdentifierExpr(var));
            sqlInListExpr.setTargetList(values.stream().map(this::convert).collect(Collectors.toList()));
            return sqlInListExpr;
        }

        private SQLInListExpr notIn(String var, Collection<?> values) {
            SQLInListExpr sqlInListExpr = new SQLInListExpr();
            sqlInListExpr.setExpr(new SQLIdentifierExpr(var));
            sqlInListExpr.setNot(!sqlInListExpr.isNot());
            sqlInListExpr.setTargetList(values.stream().map(this::convert).collect(Collectors.toList()));
            return sqlInListExpr;
        }


        private SQLExpr convert(Object value) {
            if (value instanceof Number) {
                return new SQLNumberExpr((Number) value);
            } else if (value instanceof String) {
                String str = (String) value;
                //判断是否为宏
                if (str.startsWith("@")) {
                    return new SQLVariantRefExpr(str);
                }
                if ("NULL".equalsIgnoreCase(str)) {
                    return new SQLNullExpr();
                } else {
                    return new SQLCharExpr(str);
                }
            } else if (value instanceof Boolean) {
                return new SQLBooleanExpr((Boolean) value);
            }
            return new SQLCharExpr();
        }


        private void append(SQLExpr expr, SQLBinaryOperator logic) {
            if (where == null) {
                where = expr;
            } else {
                SQLBinaryOpExpr parent = new SQLBinaryOpExpr();
                parent.setLeft(where);
                parent.setOperator(logic);
                parent.setRight(expr);
                where = parent;
            }
        }


    }
}
